/*
* Copyrigth 2008 Company All rigths reserved
* File Name: TS_School_CRUD.sql
* Created By:    Wenbin.Xu
* Created Date:   2013/7/8 16:29:48
* Description:    TS_School的增删改查存储过程
* Change History: 
* 
* */

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

Create  PROCEDURE UP_TS_School_Add
	@SchoolCode  varchar(50),
	@SchoolType  tinyint,
	@SchoolName  varchar(300),
	@ProvinceId  int,
	@AreaId  int,
	@RegionId  int,
	@TownId  int,
	@Address  varchar(500),
	@IsValid  int,
	@ZipCode  varchar(30)
AS
SET NOCOUNT ON
BEGIN
	INSERT INTO TS_School
    (
        SchoolCode,
        SchoolType,
        SchoolName,
        ProvinceId,
        AreaId,
        RegionId,
        TownId,
        Address,
        IsValid,
        ZipCode
    )
	VALUES
    (
        @SchoolCode,
        @SchoolType,
        @SchoolName,
        @ProvinceId,
        @AreaId,
        @RegionId,
        @TownId,
        @Address,
        @IsValid,
        @ZipCode)
END





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create PROCEDURE UP_TS_School_Delete
    @Idx  bigint
AS
SET NOCOUNT ON
BEGIN
	DELETE FROM  TS_School
	WHERE
    Idx=@Idx
END





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create PROCEDURE UP_TS_School_Update
	@Idx  bigint,
	@SchoolCode  varchar(50),
	@SchoolType  tinyint,
	@SchoolName  varchar(300),
	@ProvinceId  int,
	@AreaId  int,
	@RegionId  int,
	@TownId  int,
	@Address  varchar(500),
	@IsValid  int,
	@ZipCode  varchar(30)
AS
SET NOCOUNT ON
BEGIN
	UPDATE TS_School
    SET 
	SchoolCode=@SchoolCode,
	SchoolType=@SchoolType,
	SchoolName=@SchoolName,
	ProvinceId=@ProvinceId,
	AreaId=@AreaId,
	RegionId=@RegionId,
	TownId=@TownId,
	Address=@Address,
	IsValid=@IsValid,
	ZipCode=@ZipCode
    WHERE
    Idx=@Idx
END






set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create PROCEDURE UP_TS_School_View
    @Idx  bigint
AS
SET NOCOUNT ON
BEGIN
	SELECT 
    Idx,
    SchoolCode,
    SchoolType,
    SchoolName,
    ProvinceId,
    AreaId,
    RegionId,
    TownId,
    Address,
    IsValid,
    ZipCode
    FROM TS_School with(nolock)
    WHERE 
    Idx=@Idx
END





set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
Create PROCEDURE UP_TS_School_ListByPaging
(
    @pageIndex  INT,
    @pageSize   INT ,
    @recordCount INT=0 OUTPUT 
)
AS
BEGIN
	
	--calculate start index & end index
	DECLARE @startIndex  AS INT,
	        @endIndex    AS INT
	
	SELECT @startIndex = @pageSize * (@pageIndex -1) + 1,
	       @endIndex = @pageSize * @pageIndex
	
	DECLARE @sql    NVARCHAR(max)
	DECLARE @where  NVARCHAR(2000)
	SET @sql=''	
	SET @where = ''
    
	
	--calculate total record count
	SET @sql='SELECT @recordCount=COUNT(1) FROM TS_School with(nolock) WHERE (1=1) ' + @where
	EXEC sp_executesql @sql, N'@recordCount int=0 OUTPUT' , @recordCount OUTPUT 
	
	
	SET @sql = 
	    'SELECT ROW_NUMBER () OVER (ORDER BY 
            Idx ASC
        ) RowId,
        Idx,
        SchoolCode,
        SchoolType,
        SchoolName,
        ProvinceId,
        AreaId,
        RegionId,
        TownId,
        Address,
        IsValid,
        ZipCode
        FROM TS_School with(nolock)
        WHERE (1=1) ' + @where
				
	SET @sql = 'SELECT T.* FROM (' 
	+ @sql
	+ ') T where T.RowId between @startIndex and @endIndex '

	exec sp_executesql @sql,N'@startIndex int, @endIndex int',@startIndex,@endIndex 
END